package com.shop.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.shop.bean.Address;
import com.shop.dao.IAddressDao;
import com.shop.util.ConnDB;

/** 
 * @className: AddressDao.java
 * @classDescription: 
 * @function: 
 * @author: Victor
 * @createTime: 2012-11-1 下午07:15:54
 * @modifyTime: 
 * @modifyReason: 
 * @since: JDK 1.6
 */
public class AddressDaoImpl implements IAddressDao{
		
	private Connection conn = null;
	private PreparedStatement pstmt = null;
	private ResultSet rs = null;
	private String sql = "";
	private int affectedLine = 0;
	private Address addresses = null;
	
	public int createAddress(Address address) throws Exception {
		Long addressId = address.getAddressId();
		String Address = address.getAddress();
		int postcode = address.getPostcode();
		sql = "insert into address(addressId, Adress, postcode) values(?, ?, ?)";
			
		conn = ConnDB.getConn();
		pstmt = conn.prepareStatement(sql);
		pstmt.setLong(1, addressId);
		pstmt.setString(2, Address);
		pstmt.setInt(3, postcode);
		
		affectedLine = pstmt.executeUpdate();
		ConnDB.close(pstmt);
		ConnDB.close(conn);
		return affectedLine;
		}
	
	public int deleteAddressId(long addressId) throws Exception {
		conn = ConnDB.getConn();
		sql = "delete from address where addressId = ?";
		pstmt = conn.prepareStatement(sql);
		pstmt.setLong(1, addressId);
		affectedLine = pstmt.executeUpdate();
	
		ConnDB.close(pstmt);
		ConnDB.close(conn);
		return affectedLine;
		}
	
	public List<Address> queryAllAddress() throws Exception {
		sql = "select * from address";
		pstmt = conn.prepareStatement(sql);
		List<Address> addressList = new ArrayList<Address>();
		rs = pstmt.executeQuery();
		Long addressId = 0L;
		String address = "";
		int postcode = 0;
		
		while(rs.next()){
			addresses = new Address();
			addressId = rs.getLong("addressId");
			addresses.setAddressId(addressId);
			address = rs.getString("address");
			addresses.setAddress(address);
			postcode = rs.getInt("postcode");
			addresses.setPostcode(postcode);
		}
		ConnDB.close(rs);
		ConnDB.close(pstmt);
		ConnDB.close(conn);
		return addressList;
		}
	
	public List<Address> queryAllAddress(int startPage, int pageSize)
	throws Exception {
		sql = "select * from address limit(? - 1) * ?, ?";
		pstmt = conn.prepareStatement(sql);
		pstmt.setInt(1, startPage);
		pstmt.setInt(2, pageSize);
		pstmt.setInt(3, pageSize);
		List<Address> adressList = new ArrayList<Address>();
		rs = pstmt.executeQuery();
		Long addressId = 0L;
		String address = "";
		int postcode = 0;
		while(rs.next()){
			addresses = new Address();
			addressId = rs.getLong("addressId");
			addresses.setAddressId(addressId);
			address = rs.getString("address");
			addresses.setAddress(address);
			postcode = rs.getInt("postcode");
			addresses.setPostcode(postcode);
		}
		ConnDB.close(rs);
		ConnDB.close(pstmt);
		ConnDB.close(conn);
		return adressList;
		}
	
	public int updateAddress(Address address) throws Exception {
		Long addressId = address.getAddressId();
		String Address = address.getAddress();
		Integer postcode = address.getPostcode();
		
		sql = "update address set addressId = ?, Address = ? where postcode = ?";
		
		pstmt = conn.prepareStatement(sql);
		pstmt.setLong(1, addressId);
		pstmt.setString(2, Address);
		pstmt.setInt(3, postcode);
	
		affectedLine = pstmt.executeUpdate();
		ConnDB.close(pstmt);
		ConnDB.close(conn);
		return affectedLine;
	}
	
	public Address queryAddressId(long addressId) throws Exception {
		sql = "select * from address where addressId = ?";
		pstmt = conn.prepareStatement(sql);
		pstmt.setLong(1, addressId);
		rs = pstmt.executeQuery();
		
		Long addressId1 = 0L;
		String address = "";
		int postcode = 0;
		while(rs.next()){
			addresses = new Address();
			addressId1 = rs.getLong("addressId");
			addresses.setAddressId(addressId1);
			address = rs.getString("address");
			addresses.setAddress(address);
			postcode = rs.getInt("postcode");
			addresses.setPostcode(postcode);
		}
		
		ConnDB.close(rs);
		ConnDB.close(pstmt);
		ConnDB.close(conn);
		return addresses;
	}
}
